<?php
//ini_set('display_errors', 1);
//error_reporting(E_ALL);
/*
字段中有个坑：odp_teacher表中的verification我写成了verificaion，少了个t，暂时不想改了
*/

function ss( $key )
{
	return isset($_SESSION[$key])?$_SESSION[$key]:false;
}


function ss_set( $key , $value )
{
	return $_SESSION[$key] = $value;
}


function is_login()
{
	if( isset( $_COOKIE['PHPSESSID'] ) )
	{
		session_start();
		return ss('uid') > 0;
	}
	
	return false;
}

function is_admin()
{
	return ss('ulevel') > 5 ;
}

function rjoin(  $sp , $str , $array )
{
	$ret = array();
	foreach( $array as $key => $value )
	{
		$ret[] = $str.trim($value , $str ).$str;
	}
	
	return join( $sp , $ret );
}

function has_saekv()
{
	if( defined('SAE_ACCESSKEY') && substr( SAE_ACCESSKEY , 0 , 4 ) == 'kapp' ) return false;
 	return in_array( 'SaeKV' , get_declared_classes() );
	//return false;
}

if( !has_saekv() ) @mkdir( AROOT. '__lr3_kv');

function kdelete($key)
{
	if( has_saekv() )
	{
		$kv = new SaeKV();$kv->init();
		$value = $kv->delete( $key );
		return $value;
	}
}

function kget( $key )
{
	if( has_saekv() )
	{
		$kv = new SaeKV();$kv->init();
		$value = $kv->get( $key );
		if(is_NULL($value) || $value == "")
			$value=temp_kget_out( $key );
		return $value;
	}
	else
	{
		$value=temp_kget_out( $key );
		return $value;
		//写在本地文件中
		//$keyfile = AROOT. '__lr3_kv' . DS . 'kv-'.md5($key);
		//return @unserialize( @file_get_contents($keyfile) );
	}
}

function kset( $key , $value , $backup = TRUE)
{
	if( has_saekv() )
	{
		$kv = new SaeKV();$kv->init();
		//写一份到__meta_setting表中
		if ($backup){
			if( temp_kset_into( $key , $value ) ) return $kv->set( $key , $value );
		}else{
			return $kv->set( $key , $value );
		}
	}
	else
	{
		temp_kset_into( $key , $value );
		//$keyfile = AROOT. '__lr3_kv' . DS . 'kv-'.md5($key);
		//return @file_put_contents($keyfile , serialize( $value )  );
	}
}

//若kvdb中无，则直接数据库中查找，写入kvdb后再return
function get_pri_name(  $table , $db=NULL )
{
	$pri_name=kget('primary_key_name_'.$table);
	if ($pri_name=="")
	{
		$pri_name=get_table_pri( $table , $db);
		if($pri_name){
			kset( 'primary_key_name_'.$table , $pri_name, false);
		}
	}
	return $pri_name;
}

function get_my_action_code( $table , $action )
{
	$my_action_code_status=kget("my_action_code_status_".$table."_".$action);
	//初次或发生改动
	$my_code="";
	if($my_action_code_status=='' || $my_action_code_status=='1')
	{
		$my_code=get_action_code( $table , $action);
		kset( $table . "_" . $action ."_code" , $my_code ,false);
		kset("my_action_code_status_".$table."_".$action,'0',false);
	}else{
		$my_code=kget( $table . "_" . $action ."_code");
	}
	return $my_code;
}

//将teacher中的所有信息都写入kvdb，作为缓存
function get_teacher_detail( $email,$teacher_pwd,$client_name )
{

	$teacher_detail = get_db_teacher_detail($email,$teacher_pwd,$client_name);
	return $teacher_detail;

	/*
	//在更新数据时会设置这个值，标记缓存数据是否需要重新从Mysql中获取

	//个人信息发生修改时，email来标记
	$need_self_refresh = kget("teacher_detail_".s($email)."_status");

	//level发生修改
	$need_refresh = kget("teacher_detail_status");

	if($need_refresh==''||$need_refresh=='1'||$need_self_refresh==''||$need_self_refresh=='1')
	{
		$teacher_detail = get_db_teacher_detail($email,$teacher_pwd,$client_name);
		//如果有这个用户数据，才能写入kvdb，同时备份到mysql
		if($teacher_detail){
			kset("t_detail_".$client_name."_".$email,serialize($teacher_detail),true);
		}
		kset("teacher_detail_status",'0',false);
		kset("teacher_detail_".s($email)."_status",'0',false);
		return $teacher_detail;
	}else{
		$teacher_detail = kget("t_detail_".$client_name."_".$email);
		return unserialize($teacher_detail);
	}
	
	*/
}

function get_db_teacher_detail($email,$teacher_pwd,$client_name)
{
	$sql = "SELECT * FROM `odp_teacher` WHERE `email`='".s($email)."'";
	//print_r($sql);exit();
	if($teacher = get_line($sql))
	{

		$stmt = "SELECT `level` FROM `odp_level` WHERE `client_name` = '". s($client_name) ."' AND `teacher_id` = ". s($teacher['teacher_id']);
		
		if($teacher_level = get_line($stmt))
		{
			return ($teacher + $teacher_level);
		}else{
			$teacher_level=Array('level'=>'0');
			return ($teacher + $teacher_level);
		}
	}

}

function get_all_teacher()
{
	$sql = "SELECT `teacher_id`,`teacher_name`,`department_id`,`staff_id` FROM `odp_teacher`";
	return get_data($sql);

}

function get_all_nation()
{
	$sql = "SELECT * FROM `odp_nation` order by `nation_id` ASC";
	return get_data($sql);
}

function get_all_department()
{
	$sql = "SELECT * FROM `odp_department` order by `department_id` ASC";
	return get_data($sql);
}

function get_all_native_place()
{
	$sql="SELECT * FROM `odp_native_place` order by `native_place_id` ASC";
	return get_data($sql);
}

function kget_all_nation()
{
	$nations = get_all_nation();
	return $nations;
	/*
	$nation_status = kget("nation_status");
	if($nation_status=='' || $nation_status=='1')
	{
		$nations = get_all_nation();
		if($nations)
		{
			kset("nation_all",serialize($nations),true);
		}
		kset("nation_status",'0',false);
		return $nations;
	}else{
		$nations = kget("nation_all");
		return unserialize($nations);
	}
	*/
}

function kget_all_department()
{
	$departments = get_all_department();
	return $departments;
	/*
	$department_status = kget("department_status");
	if($department_status=='' ||$department_status=='1')
	{
		$departments = get_all_department();
		if($departments)
		{
			kset("department_all",serialize($departments),true);
		}
		kset("department_status",'0',false);
		return $departments;
	}else{
		$departments = kget("department_all");
		return unserialize($departments);
	}
	*/
}

function kget_all_native_place()
{
	$native_places = get_all_native_place();
	return $native_places;
	/*
	$native_place_status = kget("native_place_status");
	if($native_place_status==''||$native_place_status=='1')
	{
		$native_places = get_all_native_place();
		if($native_places)
		{
			kset("native_place_all",serialize($native_places),true);
		}
		kset("native_place_status",'0',false);
		return $native_places;
	}else{
		$native_places = kget("native_place_all");
		return unserialize($native_places);
	}
	*/
}


function get_level_table()
{
	$sql = "SELECT * FROM `odp_level`";
	return get_data($sql);
}

function get_db_list( $db = NULL )
{
	if( $data = get_data("SHOW DATABASES" , $db) )
	{
		foreach( $data as $line )
		{
			if( substr( $line['Database'] , 0 , strlen( '__meta_' ) )  ==  '__meta_' ) continue;
			$ret[] = $line['Database'];
		}
		
		return $ret;
	}
	else
		return false;
}

function table_exists( $table , $db = NULL)
{
	$ret = false;
	if( $data = get_data("SHOW TABLES" , $db ) )
		foreach( $data as $line )
			if( strtolower( $table ) == strtolower(reset( $line )) ) $ret = true;
	
	return $ret;

}

function get_table_list( $db = NULL )
{
	if( $data = get_data("SHOW TABLES" , $db ) )
	{
		foreach( $data as $line )
		{
			if( substr( reset($line) , 0 , strlen( '__meta_' ) )  ==  '__meta_' ) continue;
			$ret[] = reset( $line );
		}
		
		return $ret;
	}
	else
		return false;
}

function get_fields_info( $table , $db = NULL )
{
	if( $data = get_data("SHOW COLUMNS FROM `" . $table . "`" , $db ) )
	{
		foreach( $data as $line )
		{
			$ret[] = $line;
		}
		
		return $ret;
	}
	else
		return false;
}

function get_fields( $table , $db = NULL )
{
	if( $data = get_data("SHOW COLUMNS FROM `" . $table . "`" , $db ) )
	{
		foreach( $data as $line )
		{
			$ret[] = $line['Field'];
		}
		
		return $ret;
	}
	else
		return false;
}



function get_field_info( $table , $field , $db = NULL )
{
	
	if( $data = get_data("SHOW COLUMNS FROM `" . $table . "`" , $db ) )
	{
		foreach( $data as $line )
		{
			if( $line['Field'] == $field  )
			{
				$line['Length'] = get_field_length( $line['Type'] );
				$line['Type'] = get_field_type( $line['Type'] );
				return  $line;
			}
		}
		
		return false;
	}
	
	return false;
}

function get_project_participants($project_id)
{
	$sql=prepare('SELECT `odp_teacher`.`teacher_id`,`teacher_name`,`order`,`email`,`department_name`,`professional_title` FROM `odp_teacher`,`qlty_participant`,`odp_department` WHERE `qlty_project_id`=?i AND `odp_teacher`.`teacher_id`=`qlty_participant`.`teacher_id` AND `odp_teacher`.`department_id`=`odp_department`.`department_id` ORDER BY `order` ASC',Array($project_id));
	return get_data($sql);
}

function get_unfinished_participant_projects($teacher_id)
{
	$sql=prepare('SELECT * FROM `qlty_participant`,`qlty_project` WHERE `qlty_participant`.`qlty_project_id`=`qlty_project`.`qlty_project_id` AND `qlty_participant`.`teacher_id`=?i and `qlty_project`.`status`>6 and `qlty_project`.`status`<17',array($teacher_id));
	return get_data($sql);
}

function update_participants($project_id,$participants_arr)
{
	//先删除原来的
	$sql=prepare('DELETE FROM `qlty_participant` WHERE `qlty_project_id`=?i',Array($project_id));
	run_sql($sql);
	$stmt="INSERT INTO `qlty_participant`(`teacher_id`,`qlty_project_id`,`order`) VALUES ";
	foreach($participants_arr as $k=>$partipant)
	{
		if($k>1) $stmt .=",";
		$stmt .="(".$partipant['teacher_id'].",".$project_id.",".$k.")";
	}
	return run_sql($stmt);
}

function get_teacher_id_by_email_name($email,$name)
{
	$sql=prepare('SELECT `teacher_id` FROM `odp_teacher` WHERE `teacher_name`=?s AND`email`=?s',Array($name,$email));
	return get_var($sql);
}

function get_teacher_verified_by_email_name($email,$name)
{
	$sql=prepare('SELECT `teacher_id` FROM `odp_teacher` WHERE `teacher_name`=?s AND`email`=?s AND `is_email_verified`=1',Array($name,$email));
	return get_var($sql);
}

function get_this_status_projects($status)
{
	$sql=prepare("SELECT * FROM `qlty_project`,`odp_teacher` WHERE `status`=?i AND `qlty_project`.`director_id`=`odp_teacher`.`teacher_id`",Array($status));
	return get_data($sql);
}

function get_this_status_department_projects($status,$department_id)
{
	$sql=prepare("SELECT * FROM `qlty_project`,`odp_teacher` WHERE `status`=?i AND `qlty_project`.`director_id`=`odp_teacher`.`teacher_id` AND `odp_teacher`.`department_id`=?i",Array($status,$department_id));
	return get_data($sql);
}

function get_teacher_detail_by_id($teacher_id)
{
	$sql=prepare("SELECT * FROM `odp_teacher`,`odp_department` WHERE `teacher_id`=?i AND `odp_teacher`.`department_id`=`odp_department`.`department_id`",Array($teacher_id));
	return get_line($sql);
}

function get_teachers_with_level($level,$review_id)
{
	$sql=prepare("SELECT temp1.`teacher_id`,temp1.`level`,temp1.`department_id`,temp1.`teacher_name`,temp1.`email`,temp1.`department_name`,temp2.`group_id`,temp2.`review_id` FROM (SELECT a.`level`,b.`teacher_id`,b.`department_id`,b.`teacher_name`,b.`email`,c.`department_name` FROM `odp_level` a,`odp_teacher` b,`odp_department` c WHERE a.`teacher_id`=b.`teacher_id` AND b.`department_id`=c.`department_id` AND a.`level`=?i AND a.`client_name`='xm') as temp1 LEFT JOIN (SELECT e.`group_id`,e.`teacher_id`,f.`review_id` FROM `rev_expert_group_re` e,`rev_group` f WHERE e.`group_id`=f.`group_id` AND f.`review_id`=?i) as temp2 on temp1.`teacher_id`=temp2.`teacher_id`",Array($level,$review_id));
	//return $sql;
	return get_data($sql);
}

function replace_into_expert_group($group_id,$experts_arr)
{
	//$experts_arr如果没有,为FALSE，就是没有全部删除了专家或者没有添加专家
	if($experts_arr!="NONE")
	{
		$values="";
		$flag=false;
		foreach($experts_arr as $expert)
		{
			if($flag){
				$values .=",";
			}
			//将专家id与评审组id组合为唯一值
			$values .="(".$expert.$group_id.",".$expert.",".$group_id.")";
			$flag=true;
		}
		//先删除
		$sql=prepare("DELETE FROM `rev_expert_group_re` WHERE `group_id`=?i",Array($group_id));
		run_sql($sql);

		$sql="REPLACE INTO `rev_expert_group_re`(`rev_expert_group`,`teacher_id`,`group_id`) VALUES ".$values;
		//return $sql;
		run_sql($sql);
		if( mysql_errno() != 0 )
			return mysql_error();
		else 
			return true;


	}else{
		$sql=prepare("DELETE FROM `rev_expert_group_re` WHERE `group_id`=?i",Array($group_id));
		
		run_sql($sql);
		if( mysql_errno() != 0 )
			return mysql_error();
		else 
			return true;
	}
}


function get_projects_detail_with_status_review($review_id,$status,$project_type)
{
	$sql=prepare("SELECT * FROM (  SELECT a.`qlty_project_id`,a.`director_id`,a.`name`,b.`group_id`,b.`review_id` FROM `qlty_project` a left join (SELECT c.`qlty_project_id`,c.`group_id`,d.`review_id` FROM `rev_project_group_re` c,`rev_group` d WHERE c.`group_id`=d.`group_id` AND d.`review_id`=?i) as b on a.`qlty_project_id`=b.`qlty_project_id` WHERE a.`status`=?i AND a.`type`=?s) AS temp1,( SELECT e.`teacher_id`,e.`teacher_name`,e.`email`,f.`department_name` FROM `odp_teacher` e,`odp_department` f WHERE e.`department_id`=f.`department_id`) as temp2 WHERE temp1.`director_id`=temp2.`teacher_id`",Array($review_id,$status,$project_type));

	
	return get_data($sql);
}


function replace_into_project_group($group_id,$projects_arr)
{
	//$experts_arr如果没有,为FALSE，就是没有全部删除了专家或者没有添加专家
	if($projects_arr!="NONE")
	{
		$values="";
		$flag=false;
		foreach($projects_arr as $project)
		{
			if($flag){
				$values .=",";
			}
			//将专家id与评审组id组合为唯一值
			$values .="(".$project.$group_id.",".$project.",".$group_id.")";

			$flag=true;
		}
		//先删除
		//$sql="DELETE FROM `rev_project_group_re` WHERE ".$delete_str;
		$sql=prepare("DELETE FROM `rev_project_group_re` WHERE `group_id`=?i",Array($group_id));
		run_sql($sql);

		$sql="REPLACE INTO `rev_project_group_re`(`rev_project_group`,`qlty_project_id`,`group_id`) VALUES ".$values;
		//return $sql;
		run_sql($sql);
		if( mysql_errno() != 0 )
			return mysql_error();
		else 
			return true;


	}else{
		$sql=prepare("DELETE FROM `rev_project_group_re` WHERE `group_id`=?i",Array($group_id));
		
		run_sql($sql);
		if( mysql_errno() != 0 )
			return mysql_error();
		else 
			return true;
	}
}

function get_teacher_review_detail($teacher_id)
{
	$sql=prepare("SELECT a.`teacher_id`,b.`group_id`,b.`group_name`,c.`review_id`,c.`review_name`,c.`start_time`,c.`end_time`,c.`status_num`,c.`project_type`,c.`template_id` FROM `rev_expert_group_re` a,`rev_group` b,`rev_review` c WHERE a.`teacher_id`=?i AND a.`group_id`=b.`group_id` AND b.`review_id`=c.`review_id`",Array($teacher_id));

	return get_data($sql);
}

function get_projects_with_group_id($group_id)
{

	$sql=prepare("SELECT * FROM `rev_project_group_re` a,`qlty_project` b WHERE a.`qlty_project_id`=b.`qlty_project_id` AND a.`group_id`=?i",Array($group_id));
	
	return get_data($sql);
}

function update_rev_results($qlty_project_id,$expert_id,$review_id,$rev_result_array)
{
	$sql=PREPARE("DELETE FROM `rev_result` WHERE `qlty_project_id`=?i AND `expert_id`=?i AND `review_id`=?i",Array($qlty_project_id,$expert_id,$review_id));
	run_sql($sql);
	if( mysql_errno() != 0 )
	{

		return mysql_error();
	}
	else
	{
		//删除后重新插入所有数据
		$stmt="INSERT INTO `rev_result`(`qlty_project_id`,`expert_id`,`review_id`,`group_id`,`node_id`,`comment`,`point`) VALUES ";
		$flag=false;
		foreach($rev_result_array as $result)
		{
			if($flag)
				$stmt .=",";

			//数组最后一个元素为node_type
/*			if($result[6])
			{
				//comment类型，point为空
				$stmt .="(".$result[0].",".$result[1].",".$result[2].",".$result[3].",".$result[4].",'".$result[5]."','')";
			}else
			{
				$stmt .="(".$result[0].",".$result[1].",".$result[2].",".$result[3].",".$result[4].",'',".$result[5].")";
			}*/
			$stmt .="(".$result[0].",".$result[1].",".$result[2].",".$result[3].",".$result[4].",'".$result[5]."',".$result[6].")";

			$flag=true;
		}
		//return $stmt;
		run_sql($stmt);
		if( mysql_errno() != 0 )
			return mysql_error();
		else
			return true;
	}
}


function get_rev_results_detail($review_id)
{
	/*
	$sql=PREPARE("SELECT * FROM"
					." (SELECT f.`teacher_id`,g.`review_id`,g.`group_id`,i.`name`,i.`director_id`,i.`qlty_project_id`,i.`type` "
							." FROM `rev_expert_group_re` f,`rev_group` g,`rev_project_group_re` h,`qlty_project` i "
							." WHERE f.`group_id`=g.`group_id` AND g.`group_id`=h.`group_id` AND h.`qlty_project_id`=i.`qlty_project_id`) exp"
				." LEFT JOIN "
					." (SELECT t1.`review_id`,t1.`review_name`,t1.`template_id`,t1.`node_id`,t1.`node_name`,t1.`node_description`,t2.`comment`,t2.`point`,t2.`expert_id` FROM "
						." (SELECT a.`review_id`,a.`review_name`,a.`template_id`,b.`node_id`,b.`node_name`,b.`node_description` "
							." FROM `rev_review` a,`rev_template_node` b "
							." WHERE a.`review_id`=?i AND a.`template_id`=b.`template_id`) t1"
						." LEFT JOIN `rev_result` t2 on t1.`node_id`=t2.`node_id`) rev"
				." on exp.`review_id`=rev.`review_id` AND exp.`teacher_id`=rev.`expert_id`"
			,Array($review_id));
			*/

	$sql=PREPARE("SELECT * FROM "
				."(SELECT DISTINCT * FROM"
					." (SELECT f.`teacher_id`,g.`review_id`,g.`group_id`,i.`name`,i.`director_id`,i.`qlty_project_id`,i.`type`,i.`status` "
							." FROM `rev_expert_group_re` f,`rev_group` g,`rev_project_group_re` h,`qlty_project` i "
							." WHERE f.`group_id`=g.`group_id` AND g.`group_id`=h.`group_id` AND h.`qlty_project_id`=i.`qlty_project_id`)"
							." as exp,"
					." (SELECT a.`review_id` as `revid`,a.`review_name`,a.`status_num`,a.`template_id`,b.`node_id`,b.`node_type`,b.`node_name`,b.`node_description`,b.`score` "
							." FROM `rev_review` a,`rev_template_node` b "
							." WHERE a.`review_id`=?i AND a.`template_id`=b.`template_id`)"
							." as tmp "
						."WHERE exp.`review_id`=tmp.`revid` )"
					." as t1 LEFT JOIN"
					." `rev_result` ON t1.`node_id`=`rev_result`.`node_id` AND t1.`teacher_id`=`rev_result`.`expert_id` AND t1.`review_id`=`rev_result`.`review_id` AND t1.`qlty_project_id`=`rev_result`.`qlty_project_id`"
					
				
				,Array($review_id));


	//return $sql;
	return get_data($sql);

}

function get_rev_results_detail_with_expert_id($review_id,$expert_id)
{

	$sql=PREPARE("SELECT * FROM "
				."(SELECT DISTINCT * FROM"
					." (SELECT f.`teacher_id`,g.`review_id`,g.`group_id`,i.`name`,i.`director_id`,i.`qlty_project_id`,i.`type`,i.`status` "
							." FROM `rev_expert_group_re` f,`rev_group` g,`rev_project_group_re` h,`qlty_project` i "
							." WHERE f.`group_id`=g.`group_id` AND g.`group_id`=h.`group_id` AND h.`qlty_project_id`=i.`qlty_project_id` AND f.`teacher_id`=?i AND g.`review_id`=?i)"
							." as exp,"
					." (SELECT a.`review_id` as `revid`,a.`review_name`,a.`status_num`,a.`template_id`,b.`node_id`,b.`node_type`,b.`node_name`,b.`node_description`,b.`score` "
							." FROM `rev_review` a,`rev_template_node` b "
							." WHERE a.`review_id`=?i AND a.`template_id`=b.`template_id`)"
							." as tmp "
						."WHERE exp.`review_id`=tmp.`revid` )"
					." as t1 LEFT JOIN "
					."`rev_result`"
					." ON t1.`node_id`=`rev_result`.`node_id` AND t1.`teacher_id`=`rev_result`.`expert_id` AND t1.`review_id`=`rev_result`.`review_id` AND t1.`qlty_project_id`=`rev_result`.`qlty_project_id` "
				
				,Array($expert_id,$review_id,$review_id));


	//return $sql;
	return get_data($sql);

}

function get_all_teachers_detail()
{
	$sql="SELECT * FROM `odp_teacher` a,`odp_department` b WHERE a.`department_id`=b.`department_id`";
	return get_data($sql);
}

function get_review_id_by_project_id($qlty_project_id)
{
	$sql=PREPARE('SELECT `review_id` FROM `rev_project_group_re` a,`rev_group` b WHERE a.`group_id`=b.`group_id` AND a.`qlty_project_id`=?i',Array($qlty_project_id));

	return get_var($sql);
}

function get_project_funds($teacher_id)
{
	$sql = PREPARE("SELECT a.`qlty_project_id`,a.`name`,a.`rank`,a.`type`,a.`category`,a.`apply_funds`,sum(b.`fund_num`) as sum_fund,b.`director_checked` FROM `qlty_project` a left join (SELECT * FROM `qlty_funds` WHERE `director_checked`<>2) as b on a.`qlty_project_id`=b.`qlty_project_id` WHERE a.`status`>6 AND a.`director_id`=?i group by a.`qlty_project_id`,a.`name`,a.`category`,a.`apply_funds`",Array($teacher_id));
	//return $sql;
	return get_data($sql);
}

function get_project_sum_fund($project_id)
{
	$sql =PREPARE("SELECT sum(`fund_num`) as sum_fund FROM `qlty_funds` WHERE `qlty_project_id`=?i AND `director_checked`=1 AND `sign`=1 AND `sign_time`>0",Array($project_id));
	return get_line($sql);
}

function get_project_apply_sum_fund($project_id)
{
	$sql =PREPARE("SELECT sum(`fund_num`) as sum_fund FROM `qlty_funds` WHERE `qlty_project_id`=?i AND `director_checked`<>2",Array($project_id));
	return get_line($sql);
}

function get_fund_records_teacher_with_project_id($project_id)
{
	$sql=PREPARE("SELECT a.`teacher_name`,b.`teacher_id`,b.`fund_id`,b.`fund_content`,b.`fund_num`,b.`time`,b.`ticket_url`,b.`director_checked`,b.`sign`,b.`sign_time` FROM `odp_teacher` a,`qlty_funds` b WHERE a.`teacher_id`=b.`teacher_id` AND b.`qlty_project_id`=?i",Array($project_id));
	return get_data($sql);
}

function get_participant_project_funds($teacher_id)
{
	$sql = PREPARE("SELECT a.`qlty_project_id`,a.`name`,a.`rank`,a.`type`,a.`category`,a.`apply_funds`,sum(b.`fund_num`) as sum_fund, b.`director_checked` FROM (SELECT a2.`teacher_id`,a1.`qlty_project_id`,a1.`name`,a1.`type`,a1.`rank`,a1.`category`,a1.`apply_funds`,a1.`status` FROM `qlty_project` a1,`qlty_participant` a2 WHERE a1.`status`>6 AND a2.`teacher_id`=?i AND a1.`qlty_project_id`=a2.`qlty_project_id`) as a left join (SELECT * FROM `qlty_funds` WHERE `director_checked`<>2) as b on a.`qlty_project_id`=b.`qlty_project_id` group by a.`qlty_project_id`,a.`name`,a.`category`,a.`apply_funds`",Array($teacher_id));
	//return $sql;
	return get_data($sql);
}

function get_unsign_fund_with_project_id($qlty_project_id)
{
	$sql=PREPARE("SELECT * FROM `qlty_funds` WHERE `qlty_project_id`=?i AND `director_checked`<>2 AND `sign`=0",Array($qlty_project_id));
	return get_line($sql);
}

function get_unchecked_fund_with_project_id($qlty_project_id)
{
	$sql=PREPARE("SELECT a.`fund_id`,a.`fund_title`,a.`fund_content`,a.`teacher_id`,a.`fund_num`,b.`teacher_name` FROM `qlty_funds` a, `odp_teacher` b WHERE a.`teacher_id`=b.`teacher_id` AND a.`qlty_project_id`=?i AND a.`director_checked`=0",Array($qlty_project_id));
	return get_line($sql);
}

function get_teachers_by_name($teacher_name)
{
	//匹配姓名（中文）时出现不匹配的也返回的问题，采用自写sql加binary的方法
	$sql=PREPARE("SELECT `teacher_id`,`teacher_name`,`email`,`email_verificaion_code`,`is_imported` FROM `odp_teacher` WHERE `teacher_name`= BINARY ?s",Array($teacher_name));
	return get_data($sql);
}

function get_teacher_pwd_null($code)
{
	$sql=PREPARE("SELECT `teacher_id` FROM `odp_teacher` WHERE `teacher_pwd` is NULL AND `email_verificaion_code`=?s",Array($code));
	return get_var($sql);
}

function init_pwd($newpwd,$code)
{
	$sql=PREPARE("UPDATE `odp_teacher` SET `teacher_pwd`=?s WHERE `email_verificaion_code`=?s",Array($newpwd,$code));
	run_sql($sql);
	if( mysql_errno() != 0 )
		return mysql_error();
	else
		return true;	
}

function get_all_projects_detail()
{
	$sql="SELECT * FROM `odp_teacher` a,`qlty_project` b WHERE a.`teacher_id`=b.`director_id`";
	return get_data($sql);
}

function get_less_than_the_status_projects($status)
{
	$sql=prepare("SELECT * FROM `qlty_project`,`odp_teacher` WHERE `status`<=?i AND `status`>0 AND `qlty_project`.`director_id`=`odp_teacher`.`teacher_id`",Array($status));
	return get_data($sql);
}

function get_project_review_suggestion($project_id,$review_id,$group_id)
{
	$sql=prepare("SELECT * FROM `rev_result`,`rev_template_node` WHERE `qlty_project_id`=?i AND `review_id`=?i AND `group_id`=?i AND `node_type`=1 AND `rev_result`.`node_id`=`rev_template_node`.`node_id`",array($project_id,$review_id,$group_id));
	// return $sql;
	return get_data($sql);
}